USE CourierDatabase
GO
--ALL THIS PROCEDURES ARE USED FOR PROCESSING THE INFO IN COURIER_DETAIL TABLE.
-- ADD COURIER INTO COURIER_DETAIL TABLE
CREATE PROCEDURE ADD_COURIER
@Courier_Name VARCHAR(15),
 @s_name VARCHAR (15),
 @s_address VARCHAR (20),
 @s_phone INT,
 @r_name VARCHAR (15),
 @r_address VARCHAR(20),
 @r_phone INT,
 @amount INT,
 @status INT,
 @login_name VARCHAR(15),
 @weight FLOAT,
 @senderprovince INT,
 @receiverprovince INT,
 @date DATE
 AS
 BEGIN
	INSERT INTO CourierDatabase.dbo.Courier_Details(Courier_Name, Sender_Name, Sender_Address, Sender_Phone, Receiver_name, Receiver_Address,Receiver_Phone,Courier_Status,Amount,Login_Name,Weight_,SenderProvince,ReceiverProvince,Time_Created) values (@Courier_Name ,@s_name, @s_address, @s_phone, @r_name, @r_address, @r_phone, @status, @amount, @login_name, @weight,@senderprovince,@receiverprovince,@date)
 END
 GO
-- DELETE COURIER BY COURIER NAME
CREATE PROCEDURE DELETE_COURIER
@Courier_Name VARCHAR(15)
AS
BEGIN
	DELETE FROM CourierDatabase.dbo.Courier_Details
	WHERE @Courier_Name = Courier_Details.Courier_Name

END
GO
-- UPDATE COURIER AS A DISPATCHING DEPARTMENT EMPLOYEE
CREATE PROCEDURE UPDATE_DISPATCHING_DEPARTMENT
@Courier_Name VARCHAR(15),
@status INT
AS
BEGIN
	UPDATE CourierDatabase.dbo.Courier_Details 
	SET Courier_Status = @status
	WHERE Courier_Name = @Courier_Name
END
GO

-- UPDATE COURIER AS A WORKING DEPARTMENT EMPLOYEE
CREATE PROCEDURE UPDATE_WORKING_DEPARTMENT
 @Courier_Name VARCHAR(15),
 @Time_Created DATE,
 @login_name VARCHAR(15),
 @s_name VARCHAR (15),
 @s_address VARCHAR (20),
 @s_phone INT,
 @r_name VARCHAR (15),
 @r_address VARCHAR(20),
 @r_phone INT,
 @status INT,
 @amount FLOAT,
 @weight FLOAT,
 @Sprovince INT,
 @Rprovince INT
 AS
 BEGIN
	UPDATE CourierDatabase.dbo.Courier_Details 
	SET 
		Courier_Name= @Courier_Name,
		Sender_Name = @s_name,
		Sender_Address=@s_address,
		Sender_Phone=@s_phone,
		Receiver_Address=@r_address,
		Receiver_name=@r_name,
		Receiver_Phone=@r_phone,
		Amount =@amount,
		Courier_Status = @status,
		Login_Name = @login_name,
		Weight_= @weight,
		SenderProvince= @Sprovince,
		ReceiverProvince=@Rprovince,
		Time_Created=@Time_Created
	WHERE Courier_Details.Courier_Name = @Courier_Name	
 END
 GO

-- GET THE INFO OF COURIER BY ID
CREATE PROCEDURE INFO_COURIER
@Courier_Name VARCHAR(15)
AS
BEGIN
	SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @Courier_Name = Courier_Name
END
GO
-- CHECKING WHETHER THIS COURIER EXISTS
CREATE PROCEDURE	CHECK_COURIER
@courier_Name VARCHAR(15),
@found INT OUTPUT
AS
BEGIN
	SET @found=0
	DECLARE @check_Name INT
	SET @check_Name =(SELECT COUNT(*) FROM Courier_Details WHERE @courier_Name =Courier_Details.Courier_Name)
	IF(@check_Name!=0)
		BEGIN
			SET @found=1
		END	
END
GO
-- FIND COURIER BY ID
CREATE PROCEDURE FIND_COURIER_BY_NAME
@Courier_Name VARCHAR(15)
AS
BEGIN
	SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @Courier_Name = Courier_Name
END
GO
-- FIND COURIER BY LOGIN NAME
CREATE PROCEDURE FIND_COURIER_BY_LOGIN_NAME
@Login_Name VARCHAR(15)
AS
BEGIN
	SELECT * FROM Courier_Details WHERE @Login_Name = Login_Name
END
GO

-- procedure for finding
CREATE PROCEDURE FINDING
@S_name VARCHAR (15),
@R_name VARCHAR (15),
@login_name VARCHAR (15)
AS
BEGIN
	IF(@login_name = '')
		BEGIN
			IF(@S_name = '')
				BEGIN
					SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @R_name= Receiver_name
				END
			ELSE
				BEGIN
					IF (@R_name='') 
						BEGIN 
							SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @S_name = Sender_Name
						END
					ELSE 
						BEGIN
							SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @S_name = Sender_Name AND @R_name=Receiver_name
						END
				END
		END
	ELSE 
		BEGIN
			IF(@S_name ='')
				BEGIN
					IF(@R_name = '')
						BEGIN
							SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @login_name = Login_Name
						END
					ELSE 
						BEGIN
							SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @login_name = Login_Name AND @R_name =Receiver_name
						END
				END
			ELSE 
				BEGIN
					IF(@R_name='')
						BEGIN
							SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @login_name = @login_name AND @S_name=Sender_Name
						END
					ELSE SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @login_name = @login_name AND @S_name = Sender_Name AND @R_name = Receiver_name
				END
		END
END
GO
--GET DISTANCE FROM SENDER PROVINCE TO RECEIVER PROVINCE
CREATE PROCEDURE GET_DISTANCE
@S_province INT,
@R_province INT,
@distance INT OUTPUT
AS
BEGIN
	SET @distance= (SELECT Distance FROM CourierDatabase.dbo.Road WHERE @S_province = Road.Point_A AND @R_province = Road.Point_B)
END
GO

-- GET A COURIER THAT HASN'T DONE YET.
CREATE PROCEDURE GET_COUR_HAS_NOT_DONE
AS
BEGIN
	SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE Courier_Status <> ReceiverProvince
END
GO

-- GET ALL COURIER
CREATE PROCEDURE GET_ALL
AS
BEGIN
	SELECT * FROM CourierDatabase.dbo.Courier_Details
END
GO

-- FINDING--
CREATE PROCEDURE FIND
@S_name VARCHAR (15),
@R_name VARCHAR (15),
@login_name VARCHAR (15),
@date DATE
AS
DECLARE @CHECK INT
BEGIN
	SET @CHECK = (SELECT COUNT(*) FROM CourierDatabase.dbo.Courier_Details WHERE @date =Courier_Details.Time_Created)
	IF(@login_name='')
		BEGIN
			IF(@S_name='')
				BEGIN
					IF(@R_name='')
						BEGIN
							IF(@CHECK<>0) SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE  @date=Courier_Details.Time_Created	
						END
					ELSE
						BEGIN
							IF(@CHECK<>0) SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @date=Time_Created AND @R_name =Receiver_Name
							ELSE SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE  @R_name =Receiver_Name
						END
				END
			ELSE 
				BEGIN
					IF(@R_name='')
						BEGIN
							IF(@CHECK<>0) SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE  @date=Courier_Details.Time_Created	AND @S_name=Sender_Name
							ELSE SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @S_name =Sender_Name
						END
					ELSE
						BEGIN
						IF(@CHECK<>0) SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @date=Courier_Details.Time_Created	AND @S_name=Sender_Name AND @R_name =Receiver_Name
						ELSE SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @S_name=Sender_Name AND @R_name =Receiver_Name
						END
				END
		END
	ELSE
		BEGIN
			IF(@S_name='')
				BEGIN
					IF(@R_name='')
						BEGIN
							IF(@CHECK<>0) SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @login_name=Login_Name AND @date =Time_Created
							ELSE SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE  @login_name=Login_Name
						END
					ELSE
						BEGIN
							IF(@CHECK<>0) SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @login_name=Login_Name AND @date =Time_Created AND @R_name =Receiver_Name
							ELSE SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @login_name=Login_Name AND @R_name =Receiver_Name
						END
				END
			ELSE
				BEGIN
					IF(@R_name='')
						BEGIN
							IF(@CHECK<>0) SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @login_name=Login_Name AND @S_name = Sender_Name AND @date =Time_Created
							
							ELSE SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @login_name=Login_Name AND @S_name=Sender_Name
						END
					ELSE
						BEGIN
							IF(@CHECK<>0) SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @login_name=Login_Name AND @S_name=Sender_Name AND @R_name=Receiver_Name AND @date=Time_Created
							ELSE SELECT * FROM CourierDatabase.dbo.Courier_Details WHERE @login_name=Login_Name AND @S_name=Sender_Name AND @R_name=Receiver_Name
						END
				END
		END
END
GO
-- ADD DISTANCE
CREATE PROCEDURE ADD_DISTANCE
@Point_A INT,
@Point_B INT,
@distance FLOAT
AS
	BEGIN
		INSERT INTO CourierDatabase.dbo.Road(Point_A,Point_B,Distance) VALUES(@Point_A,@Point_B,@distance)
	END
GO
--ADD LOCATION
CREATE PROCEDURE ADD_LOCATION
@location VARCHAR(50)
AS
	BEGIN
		INSERT INTO CourierDatabase.dbo.Point(Point_Name) values (@location)
	END
GO
--CHECK LOCATION
CREATE PROCEDURE CHECK_LOCATION
@Location VARCHAR(50),
@found INT output
AS
	SET @found=0
	DECLARE @check INT
	SET @check = (SELECT COUNT (*) FROM Point WHERE @Location=Point_Name)
	BEGIN
		IF(@check!=0) SET @found =1
	END
GO
--COST OF DISTANCE
CREATE PROCEDURE COST_TABLE
AS
BEGIN
	SELECT * FROM CourierDatabase.dbo.Charges
END
GO
--// calculate the courier bases on weight and distance
CREATE PROCEDURE CALULATE
	@distance FLOAT,
	@return INT OUTPUT,
	@S_province INT,
	@R_province INT
AS
BEGIN
	
	IF(@S_province = @R_province) SET @return=1
	ELSE IF(@DISTANCE <=500 and @distance>0) SET @return=2
	ELSE IF(@distance>500 AND @distance <=1000) SET @return=3
	ELSE IF(@distance>1000 AND @distance<=2000) SET @return =4
	ELSE IF(@distance>2000) SET @return=5
	ELSE SET @return=6
END
GO
-- GET THE COST BASE ON THE ROAD
CREATE PROCEDURE GETCOST
	@distance INT,
	@price FLOAT OUTPUT,
	@addition FLOAT OUTPUT
AS
BEGIN
	SET @price =(SELECT PRICE FROM CourierDatabase.dbo.Charges WHERE @distance = Distance)
	SET @addition =(SELECT ADDITION FROM CourierDatabase.dbo.Charges WHERE @distance = Distance)
END
GO
--UPDATE DISTANCE
CREATE PROCEDURE UPDATE_DISTANCE
@DISTANCE FLOAT,
@ID INT
AS
BEGIN
	UPDATE Road
	SET
		Distance =@DISTANCE
	WHERE @ID=Road_Id
END
GO
--UPDATE TRADE
CREATE PROCEDURE UPDATE_TRADE
@PRICE FLOAT,
@ADDITION FLOAT,
@DISTANCE INT
AS
BEGIN
	UPDATE Charges
	SET 
		Price =@PRICE,
		Addition = @ADDITION
	WHERE Distance =@DISTANCE
END
GO
